package com.xinshujia.wxxcx.mapper;

import com.xinshujia.wxxcx.pojos.info.AccelerationInfo;
import com.xinshujia.wxxcx.pojos.info.DeviceInfo;
import com.xinshujia.wxxcx.pojos.info.FunctionResultInFo;
import com.xinshujia.wxxcx.pojos.vo.XThreshold;
import com.xinshujia.wxxcx.pojos.vo.YThreshold;
import com.xinshujia.wxxcx.pojos.vo.ZThreshold;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * @Author Cui Han
 * @Date 2024/5/18 17:38
 * @Description: TODO
 * @Version 1.0
 */
@Mapper
public interface AccelerationMapper {

    @Select("select `Time` ,`Peak`, `DeviceGUID` from AccelerationStatistics where DeviceGUID = #{device} and `Channel` = 0 ORDER BY `Time` DESC LIMIT 1 ")
    AccelerationInfo selectacceleration(DeviceInfo device);

    @Select("select `Time` ,`Peak`, `DeviceGUID` from AccelerationStatistics where DeviceGUID = #{device} and `Channel` = 0 ORDER BY `Time` DESC LIMIT 0, 30 ")
    List<AccelerationInfo> selectaccelerations(DeviceInfo device);


    @Select("select MAX(Peak) as 'xThreshold' from AccelerationStatistics where `Channel` = 0 AND DeviceGUID = #{deviceGUID} and `Time` BETWEEN #{time} AND #{endDate} ")
    XThreshold seleltXPeak(String deviceGUID, Date time, Date endDate);

    @Select("select MAX(Peak) as 'yThreshold'from AccelerationStatistics where `Channel` = 1 AND DeviceGUID = #{deviceGUID} and `Time` BETWEEN #{time} AND #{endDate} ")
    YThreshold seleltYPeak(String deviceGUID, Date time, Date endDate);

    @Select("select MAX(Peak) as 'zThreshold' from AccelerationStatistics where `Channel` = 2 AND DeviceGUID = #{deviceGUID} and `Time` BETWEEN #{time} AND #{endDate} ")
    ZThreshold seleltZPeak(String deviceGUID, Date time, Date endDate);

    @Select("WITH RankedData AS (\n" +
            "SELECT\n" +
            "Peak,\n" +
            "ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNum\n" +
            "FROM\n" +
            "AccelerationStatistics\n" +
            "WHERE\n" +
            "Channel = 0\n" +
            "AND DeviceGUID = #{device}\n" +
            "AND Time < #{time}\n" +
            ")\n" +
            "SELECT Peak\n" +
            "FROM RankedData\n" +
            "WHERE RowNum = 5")
    AccelerationInfo selectpeak(String device, Date time);

    @Select("SELECT DISTINCT DeviceGUID FROM AccelerationStatistics WHERE Time >= DATE_SUB(NOW(), INTERVAL 1 MINUTE);")
    List<String> selectOnlineDeviceGuids();

    @Select("select Peak from AccelerationStatistics where DeviceGUID = #{guid} and `Channel` = 0 ORDER BY `Time` DESC LIMIT 1  ")
    FunctionResultInFo selectTimePeak(String guid);

    @Select("select Peak from AccelerationStatistics where DeviceGUID = #{guid} and `Channel` = 0 and Time BETWEEN DATE_SUB(#{date1}, INTERVAL 10 SECOND) AND NOW();")
    FunctionResultInFo selectTimePeaks(Date date1, String guid);
}
